package com.dao.impl;

import com.dao.SupplierDao;
import com.db.ConnectionFactory;
import com.entry.Goods;
import com.entry.Supplier;
import com.util.PageModel;

import java.sql.*;
import java.util.ArrayList;

public class SupplierDaoImpl implements SupplierDao {
    @Override
    public PageModel<Supplier> findAll(int pageSize, int pageNo) {
        ArrayList<Supplier> list = new ArrayList<Supplier>();
        Connection con = null;
        PreparedStatement pre = null;
        ResultSet rs = null;
        String sql = "select sup_id,sup_name,sup_address from supplier";
        PageModel<Supplier> pm = new PageModel<Supplier>(pageSize, pageNo, total(sql));
        try {
            con = ConnectionFactory.getConnection();
            pre = con.prepareStatement(sql+" limit " + (pm.getPageNo() - 1) * pm.getPageSize() + "," + pm.getPageSize());
            rs = pre.executeQuery();
            while (rs.next()){
                Supplier supplier = new Supplier();
                supplier.setSup_id(rs.getInt(1));
                supplier.setSup_name(rs.getString(2));
                supplier.setSup_address(rs.getString(3));
                list.add(supplier);
            }
            pm.setData(list);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                if (rs != null)
                    rs.close();
                if (pre != null)
                    pre.close();
                if (con != null)
                    con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return pm;
    }

    @Override
    public int add(Supplier supplier) {
        int row = 0;
        Connection con = null;
        PreparedStatement pre = null;
        String sql = "insert into supplier(sup_name,sup_address) values(?,?)";
        try {
            con = ConnectionFactory.getConnection();
            pre = con.prepareStatement(sql);
            pre.setString(1,supplier.getSup_name());
            pre.setString(2,supplier.getSup_address());
            row = pre.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try{
                if (pre != null){pre.close();}
                if (con != null){con.close();}
            }catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return row;
    }

    @Override
    public int update(Supplier supplier) {
        int row = 0;
        Connection con = null;
        PreparedStatement pre = null;
        String sql = "update supplier set sup_address=? where sup_name=?";
        try {
            con = ConnectionFactory.getConnection();
            pre = con.prepareStatement(sql);
            pre.setString(2,supplier.getSup_name());
            pre.setString(1,supplier.getSup_address());
            row = pre.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try{
                if (pre != null){pre.close();}
                if (con != null){con.close();}
            }catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return row;
    }

    @Override
    public Supplier findId(String name) {
        Supplier supplier = null;
        Connection con = null;
        PreparedStatement pre = null;
        ResultSet rs = null;
        String sql = "select sup_name,sup_address from supplier where sup_name=? limit 1";
        try {
            con = ConnectionFactory.getConnection();
            pre = con.prepareStatement(sql);
            pre.setString(1,name);
            rs = pre.executeQuery();
            if(rs.next()){
                supplier = new Supplier();
                supplier.setSup_name(rs.getString(1));
                supplier.setSup_address(rs.getString(2));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try{
                if (rs != null){rs.close();}
                if (pre != null){pre.close();}
                if (con != null){con.close();}
            }catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return supplier;
    }

    @Override
    public int delete(String name) {
        int row = 0;
        Connection con = null;
        PreparedStatement pre = null;
        String sql = "delete from supplier where sup_name=?";
        try {
            con = ConnectionFactory.getConnection();
            pre = con.prepareStatement(sql);
            pre.setString(1,name);
            row = pre.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try{
                if (pre != null){pre.close();}
                if (con != null){con.close();}
            }catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return row;
    }

    public int total(String sql) {
        int recordCount = 0;
        String countsql = "select count(*) "
                + sql.substring(sql.indexOf("from"), sql.length());

        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            con = ConnectionFactory.getConnection();
            stmt = con.createStatement();
            rs = stmt.executeQuery(countsql);
            if (rs.next()) {
                recordCount = rs.getInt(1);
            }
        }catch (Exception e) {
            e.printStackTrace();
        } finally {
            try{
                if (rs != null){rs.close();}
                if (stmt != null){stmt.close();}
                if (con != null){con.close();}
            }catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return recordCount;
    }
}
